import os
import sys

cur_dir = os.path.dirname(__file__)
project_path = os.path.dirname(cur_dir)
# 添加项目路径
if project_path not in sys.path:
    sys.path.append(project_path)
# 移除当前目录，避免包名和文件名重复时识别错误
if cur_dir in sys.path:
    sys.path.remove(cur_dir)
import pandas as pd
import pymysql
from common.config import config
from common.logger import logger


class MySQLClient:
    def __init__(self):
        try:
            self.connection = pymysql.connect(
                host=config.MYSQL_HOST,
                user=config.MYSQL_USER,
                password=config.MYSQL_PASSWORD,
                database=config.MYSQL_DATABASE
            )
            self.cursor = self.connection.cursor()
            logger.info("MySQL 连接成功")
        except pymysql.MySQLError as e:
            logger.error(f"MySQL 连接失败: {e}")
            raise

    def init_insert_data(self, csv_path):
        data = pd.read_csv(csv_path)
        for _, row in data.iterrows():
            insert_query = "INSERT INTO qa (subject, q, a) VALUES (%s, %s, %s)"
            self.cursor.execute(insert_query, (row['学科名称'], row['问题'], row['答案']))
        self.connection.commit()
        logger.info("数据插入成功")

    def fetch_all_questions(self):
        """
        获取所有问题的ID和内容
        :return: 问题ID和内容列表
        """
        self.cursor.execute("SELECT id, a FROM qa")
        return self.cursor.fetchall()

    def get_answer(self, question_id):
        """
        获取指定问题的答案
        :param question_id: 问题ID
        :return: 答案
        """
        try:
            self.cursor.execute("SELECT a FROM qa WHERE id=%s", (question_id,))
            result = self.cursor.fetchone()
            return result[0] if result else None
        except pymysql.MySQLError as e:
            logger.error(f"答案获取失败: {e}")
            return None

    def close(self):
        try:
            self.connection.close()
            logger.info("MySQL 连接已关闭")
        except pymysql.MySQLError as e:
            logger.error(f"关闭连接失败: {e}")


if __name__ == '__main__':
    mysql_client = MySQLClient()
    # mysql_client.init_insert_data(to_abs_path('data/JP学科知识问答.csv'))
    res = mysql_client.fetch_all_questions()
    pass
